package jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;

public class JavaJDBC {


    public static void  main(String args[])  {
        String conn = "jdbc:mysql://localhost:3306/lxf?serverTimezone=GMT%2B8";
        String name = "root";
        String password = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(conn,name,password);
            System.err.println("链接数据库");
            JdbcDO jdbcDO = new JdbcDO();
            Scanner scanner = new Scanner(System.in);
            boolean hh = true;
                while (hh){
                    System.err.println("1:查询\n"+
                            "2:删除\n"+
                            "3:添加\n"+
                            "4:更新\n"+
                            "5:退出");
                    String type = scanner.next();
                    switch (type) {
                        case "1":
                            String sql = "select * from user";
                            Statement statement = connection.createStatement();
                            ResultSet resultSet = statement.executeQuery(sql);
                            System.err.println("查询成功");
                            while (resultSet.next()) {
                                System.err.println(resultSet.getString(1)+resultSet.getString(2)+resultSet.getString(3));
                            }
                            break;
                        case "2":
                            boolean two = true;
                            while (two){
                                System.err.println("1:删除单行\n"+"2:删除多行\n"+"3:退出");
                                switch (scanner.next()){
                                    case "1":
                                        String sql1 = "delete from user where id=?";
                                        System.err.println("请输入要删除的id");
                                        String id = scanner.next();
                                        jdbcDO.setId(Integer.valueOf(id));
                                        PreparedStatement preparedStatement = connection.prepareStatement(sql1);
                                        preparedStatement.setInt(1,jdbcDO.getId());
                                        preparedStatement.executeUpdate();
                                        System.err.println("删除完成");
                                        break;
                                    case "2":
                                        boolean kk = true;
                                        while (kk){
                                            System.err.println("1:开始删除\n" +"2:退出");
                                            switch (scanner.next()){
                                                case "1":
                                                    String sql4 = "delete from user where id between ? and ?";
//                                                    System.err.println("请输入要删除的id");
//                                                    String id5 = scanner.next();
//                                                    jdbcDO.setId(Integer.valueOf(id5));
                                                    PreparedStatement preparedStatement5 = connection.prepareStatement(sql4);
//                                                    preparedStatement5.setInt(1,jdbcDO.getId());
                                                    System.err.println("请输入开始删除的行数");
                                                    preparedStatement5.setInt(1,scanner.nextInt());
                                                    System.err.println("请输入结束删除的行数");
                                                    preparedStatement5.setInt(2,scanner.nextInt());
                                                    preparedStatement5.executeUpdate();
                                                    System.err.println("删除完成");
                                                    break;
                                                case "2":
                                                    kk=false;
                                                    break;
                                                default:
                                            }
                                        }
                                        break;
                                    case "3":
                                        two = false;
                                        break;
                                    default:
                                }
                            }
                            break;
                        case "3":
                            boolean sh = true;
                            while (sh){
                                System.err.println("1:添加单条数据\n"+"2:添加多条数据\n"+"3:退出");
                                switch (scanner.next()){
                                    case "1":
                                        System.err.println("请输入id");
                                        int id1 = scanner.nextInt();
                                        System.err.println("请输入名字");
                                        String name2 = scanner.next();
                                        System.err.println("请输入年龄");
                                        int age = scanner.nextInt();
                                        jdbcDO.setId(id1);
                                        jdbcDO.setName(name2);
                                        jdbcDO.setAge(age);
                                        String sql2 = "insert into  user(id, name, age) values(?,?,?)";
                                        PreparedStatement preparedStatement1 = connection.prepareStatement(sql2);
                                        preparedStatement1.setInt(1,jdbcDO.getId());
                                        preparedStatement1.setString(2,jdbcDO.getName());
                                        preparedStatement1.setInt(3,jdbcDO.getAge());
                                        preparedStatement1.executeUpdate();
                                        System.err.println("添加完成");
                                        break;
                                    case "2":
                                        System.err.println("请输入需要添加的总数");
                                        int i = scanner.nextInt();
                                        for (int k =0;k<i;k++){
                                            System.err.println("请输入id");
                                            int id3 = scanner.nextInt();
                                            System.err.println("请输入名字");
                                            String name3 = scanner.next();
                                            System.err.println("请输入年龄");
                                            int age3 = scanner.nextInt();
                                            jdbcDO.setId(id3);
                                            jdbcDO.setName(name3);
                                            jdbcDO.setAge(age3);
                                            String sql3 = "insert into  user(id, name, age) values(?,?,?)";
                                            PreparedStatement preparedStatement3 = connection.prepareStatement(sql3);
                                            preparedStatement3.setInt(1,jdbcDO.getId());
                                            preparedStatement3.setString(2,jdbcDO.getName());
                                            preparedStatement3.setInt(3,jdbcDO.getAge());
                                            preparedStatement3.executeUpdate();
                                        }
                                        System.err.println("添加完成");
                                        break;
                                    case "3":
                                        sh = false;
                                        break;
                                }

                            }
                            break;
                        case "4":
                            System.err.println("请输入id");
                            int id4 = scanner.nextInt();
                            System.err.println("请输入年龄");
                            int age2 = scanner.nextInt();
                            jdbcDO.setId(id4);
                            jdbcDO.setAge(age2);
                            String sql3 = "update user set age= ? where id= ? ";
                            PreparedStatement preparedStatement2 = connection.prepareStatement(sql3);
                            preparedStatement2.setInt(1,jdbcDO.getAge());
                            preparedStatement2.setInt(2,jdbcDO.getId());
                            preparedStatement2.executeUpdate();
                            System.err.println("更新完成");
                            break;
                        case "5":
                            hh=false;
                            break;
                        default:
                    }
                }
            System.err.println("____________________");

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}